package com.xd.cloud.common.sql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.xd.cloud.entity.vehicle.GpsDataPo;

/**   
 * 功能描述：轨迹回放历史数据
 * @author: wangshanshan   
 * @date: 2017年12月22日 上午9:07:40 
 */
public class TrackReplayData {

	static String sql = null;  
    static DBHelper db1 = null;  
    static ResultSet ret = null;  
    
    /**
     * 根据设备号、开始/结束时间 查询该设备的历史轨迹回放数据
     * @param deviceId   设备id
     * @param startTime  查询开始时间
     * @param endTime    查询结束时间
     * @return List<GpsData>
     */
	public static List<GpsDataPo> queryTrackReplayData(String deviceId, String startTime, String endTime) {
		List<GpsDataPo> dataList = new ArrayList<GpsDataPo>();
		String sql = "select device_id,lng,lat,ifnull(speed,0) speed,ifnull(direction,0) direction,ifnull(acc,'0') acc,ifnull(mileage,0) mileage,date_format(gpstime,'%Y-%m-%d %T') gpstime,ifnull(status,'') status,ifnull(csq,'0') csq "
				+ "from jt808_gps_data where lng is not null and lat is not null and device_id = '"+deviceId+"' and create_time >= '"+startTime+"' and create_time <='"+endTime+"' order by gpstime";
		try {
			long  start = System.currentTimeMillis();
			db1 = new DBHelper(sql);
			db1.pst.execute();
			ret = db1.pst.executeQuery();//执行语句，得到结果集  
            while (ret.next()) {  
                GpsDataPo item = new GpsDataPo();
                item.setDevice_id(ret.getString("device_id"));
                item.setLng(ret.getDouble("lng"));
                item.setLat(ret.getDouble("lat"));
                item.setSpeed(ret.getDouble("speed"));
                item.setDirection(ret.getDouble("direction"));
                item.setAcc(ret.getInt("acc"));
                item.setMileage(ret.getDouble("mileage"));
                item.setGpstime_str(ret.getString("gpstime"));
                item.setStatus(ret.getString("status"));
                item.setCsq(ret.getInt("csq"));
                dataList.add(item);
            }
            ret.close();
          	long end = System.currentTimeMillis();
        	System.out.println(deviceId+"------轨迹回放数据------>>>"+dataList.size()+"条,耗时"+(end-start));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
            db1.close();//关闭连接  
		}
		return dataList;
	}
	
	public static void main(String args[]) {
		queryTrackReplayData("64939160396","2017-12-01","2017-12-30");
	}
}
